package com.greencloud.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @(#)JDBCUtil.java
 * 
 * @description: JBDC操作工具类-请谨慎使用。推荐获取hibernate的session
 * @author: 王佳  2014/07/03
 * @version: 1.0
 * @modify: MODIFIER'S NAME YYYY/MM/DD 修改内容简述
 * @Copyright: 版权信息
 */
public class JDBCUtil {

	private static String dburl = "com.mysql.jdbc.Driver";
	private static String connectionUrl = "jdbc:mysql://127.0.0.1:3306/test?allowMultiQueries=true&zeroDateTimeBehavior=convertToNull";
	private static String userName = "root";
	private static String password = "123456";

	//私有构造方法，防止外部实例化
	private JDBCUtil() {

	}

	/**
	 * 获取JDBC连接
	 * */
	public static Connection getConnection() {
		try {
			Class.forName(dburl);
			Connection connection;
			try {
				connection = DriverManager.getConnection(connectionUrl,
						userName, password);
				return connection;
			} catch (SQLException e) {

				e.printStackTrace();
			}

		} catch (ClassNotFoundException e) {

			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 执行sql语句
	 * @param  sql
	 * @return ResultSet rs
	 * */
	public static ResultSet executeQuery(String sql) {
		Connection connection = getConnection();

		PreparedStatement pstmt = null;
		try {
			pstmt = connection.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			return rs;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

		}
		return null;
	}
	
	/**
	 * 执行sql语句,
	 * @param String sql
	 * @return Boolean flag 执行语句成功与否
	 * */
	public static Boolean executeQueryOnly(String sql) {
		Connection connection = getConnection();

		PreparedStatement pstmt = null;
		try {
			pstmt = connection.prepareStatement(sql);
			//此flag表示execute 方法返回一个 boolean 值，
			//指示第一个结果的形式。必须调用 getResultSet 或 getUpdateCount 方法获取该结果，必须调用 getMoreResults 获取任何后续结果。 
			 pstmt.execute();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

		}
		return false;
	}

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		Connection connection = getConnection();
		//获取所有表名
		String tableSql = "select table_name from information_schema.tables where table_schema='website_thirdwhotel_wj' and table_type='base table'; ";
		String columnSql = "select column_name from information_schema.columns where table_schema='rbac' and table_name='menu'";

		PreparedStatement pstmt;
		try {
			pstmt = connection.prepareStatement(tableSql);
			boolean flag = pstmt.execute();
			ResultSet rs = pstmt.executeQuery();
			// Userinfo userInfo ;
			while (rs.next()) {
				// userInfo = new Userinfo();
				//System.out.println(rs.getInt(1));
				System.out.println(rs.getString(1));
			}
			System.out.println("链接成功");
		} catch (SQLException e) {

			e.printStackTrace();
		}

	}

}
